#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
delete from hive.zxedu_dws.dws_signup_count;
insert into hive.zxedu_dws.dws_signup_count
select year_code,
       month_code,
       dt,
       itcast_school_id as school_id,
       itcast_school_name as school_name,
       itcast_subject_id as subject_id,
       itcast_subject_name as subject_name,
       origin_type,
       origin_channel,
       tdepart_id,
       tdepart_name,
       case grouping(dt,month_code,year_code,origin_type,itcast_school_id,itcast_subject_id,origin_channel,
                     tdepart_id)
       when 23  then'日期，校区'
       when 151 then'月份，校区'
       when 215 then'年份，校区'
       when 7   then'日期，校区，线上线下'
       when 135 then'月份，校区，线上线下'
       when 199 then'年份，校区，线上线下'
       when 11  then'日期，学科，线上线下'
       when 139 then'月份，学科，线上线下'
       when 203 then'年份，学科，线上线下'
       when 3   then'日期，校区，学科，线上线下'
       when 131 then'月份，校区，学科，线上线下'
       when 195 then'年份，校区，学科，线上线下'
       when 13  then'日期，线上线下，来源渠道'
       when 141 then'月份，线上线下，来源渠道'
       when 205 then'年份，线上线下，来源渠道'
       when 14  then'日期，线上线下，咨询中心'
       when 142 then'月份，线上线下，咨询中心'
       when 206 then'年份，线上线下，咨询中心'
       when 15  then'日期，线上线下'
       when 143 then'月份，线上线下'
       when 207 then'年份，线上线下' end as group_type,
       count(if(payment_state='PAID',1,null)) as signup_count,
       count(id) as relationship_count,
       count(if(appeal_status = 2 or appeal_status is null,1,null)) as effective_clue_count
from hive.zxedu_dwb.dwb_relationship_detail
group by
grouping sets
((year_code,month_code,dt,itcast_school_id,itcast_school_name),
(year_code,month_code,itcast_school_id,itcast_school_name),
(year_code,itcast_school_id,itcast_school_name),
(year_code,month_code,dt,itcast_school_id,itcast_school_name,origin_type),
(year_code,month_code,itcast_school_id,itcast_school_name,origin_type),
(year_code,itcast_school_id,itcast_school_name,origin_type),
(year_code,month_code,dt,itcast_subject_id,itcast_subject_name,origin_type),
(year_code,month_code,itcast_subject_id,itcast_subject_name,origin_type),
(year_code,itcast_subject_id,itcast_subject_name,origin_type),
(year_code,month_code,dt,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name,origin_type),
(year_code,month_code,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name,origin_type),
(year_code,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name,origin_type),
(year_code,month_code,dt,origin_type,origin_channel),
(year_code,month_code,origin_type,origin_channel),
(year_code,origin_type,origin_channel),
(year_code,month_code,dt,origin_type,tdepart_id,tdepart_name),
(year_code,month_code,origin_type,tdepart_id,tdepart_name),
(year_code,origin_type,tdepart_id,tdepart_name),
(year_code,month_code,dt,origin_type),
(year_code,month_code,origin_type),
(year_code,origin_type));
"